package cn.hs.ph.dao.impl;

import cn.hs.ph.dao.CommunityDao;
import cn.hs.ph.entity.Community;
import cn.hs.ph.entity.Shop;
import cn.hs.ph.util.JDBCDruidUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class CommunityDaoImpl implements CommunityDao {
    @Override
    public int insert(Community community) {
        String sql = "insert into community values(null,?,?,?,?,?,?,?,now(),?,?,?,?,?,?)";
        Object obj[] = {
                community.getCommunityName(),
                community.getCommunityProvince(),
                community.getCommunityCity(),
                community.getCommunityDistrict(),
                community.getCommunityAddress(),
                community.getCommunityLatitude(),
                community.getCommunityLongitude(),
                community.getCommunityShopCount(),
                community.getCommunityAreaCode(),
                community.getCommunityCoordinates(),
                community.getCommunityStatus(),
                community.getCommunityUserCount(),
                community.getDistance()
        };
        return JDBCDruidUtil.update(sql, obj);
    }
    @Override
    public int delete(int communityId) {
        String sql = "delete from community where communityId=?";
        Object[] obj = new Object[]{communityId};
        return JDBCDruidUtil.update(sql, obj);
    }
    @Override
    public int update(Community community) {
        String sql = "update community set CommunityName=?,CommunityProvince=?,CommunityCity=?,CommunityDistrict=?,CommunityAddress=?,CommunityLatitude=?,CommunityLongitude=? where communityId=?";
        Object[] obj = new Object[]{community.getCommunityName(), community.getCommunityProvince(), community.getCommunityCity(), community.getCommunityDistrict(), community.getCommunityAddress(), community.getCommunityLatitude(), community.getCommunityLongitude(), community.getCommunityId()};
        return JDBCDruidUtil.update(sql, obj);
    }
    //查看关联门店操作
    public int countShop(int communityId) {
        int num=0;
        String sql="select count(1) from shop where communityId=?";
        Object[] obj={communityId};
        ResultSet res = JDBCDruidUtil.query(sql, obj);
        while (true){
            try {
                if (!res.next()) break;
                num=res.getInt(1);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }JDBCDruidUtil.close();
        return num;
    }
    //查询关联门店
    @Override
    public List<Shop> selectShop(Map<String,String> map){
        List<Shop> list = new ArrayList<>();
        //分页查询 模糊查询
        String sql = "select * from shop where 1=1";
        List<Object> params = new ArrayList<>();
        //判断是否需要进行模糊查询,门店名称
        String shopName = map.get("shopName");
        if (shopName != null && !"".equals(shopName)) {
            sql += " and shopName like ?";
            params.add("%" + shopName + "%");
        }
        sql+= " and communityId=? order by shopId desc";
        int communityId = Integer.parseInt(map.get("communityId"));
        params.add(communityId);
        //判断是否需要分页查询
        String pn = map.get("pageNum");
        if (pn != null && !"".equals(pn)) {
            sql += " limit ?,?";
            int pageNum = Integer.parseInt(pn);
            int pageSize = Integer.parseInt(map.get("pageSize"));
            params.add((pageNum - 1) * pageSize);
            params.add(pageSize);
        }
        Object[] obj = params.toArray();
        ResultSet res = JDBCDruidUtil.query(sql, obj);
        while (true) {
            try {
                if (!res.next()) break;
                Shop shop = new Shop(
                        res.getInt(1),
                        res.getDate(2),
                        res.getString(3),
                        res.getString(4),
                        res.getInt(5),
                        res.getInt(6),
                        res.getString(7),
                        res.getString(8),
                        res.getString(9),
                        res.getString(10),
                        res.getString(11),
                        res.getString(12),
                        res.getString(13),
                        res.getString(14),
                        res.getString(15),
                        res.getString(16),
                        res.getString(17),
                        res.getInt(18),
                        res.getString(19),
                        res.getString(20),
                        res.getString(21),
                        res.getString(22),
                        res.getInt(23),
                        res.getInt(24),
                        res.getInt(25)
                );
                list.add(shop);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        JDBCDruidUtil.close();
        return list;
    }
    //删除关联门店
    public int deleteShop(Map<String,String> map){
        String sqlShop="update shop set communityId=0 where shopId=?";
        int shopId = Integer.parseInt(map.get("ShopId"));
        Object objShop[]={shopId};
        String sqlCommunity="update community set communityShopCount=communityShopCount-1 where communityId=?";
        int communityId = Integer.parseInt(map.get("communityId"));
        Object objCommunity[]={communityId};
        int n1 = JDBCDruidUtil.update(sqlShop, objShop);
        int n2 = JDBCDruidUtil.update(sqlCommunity, objCommunity);
        return n1+n2;
    }
//    添加尚未关联门店
    public List<Shop> findNotAssociatedShop(Map<String,String> map) {
        List<Shop> list=new ArrayList<>();
        //通过shopName模糊查询
        List params=new ArrayList<>();
        String sql="select * from shop where 1=1 ";
        String shopName = map.get("shopName");
        if (shopName!=null&&!"".equals(shopName)){
            sql+=" and shopName like ? ";
            params.add("%"+shopName+"%");
        }
        sql+=" and (communityId is null or communityId=0) ";
        //判断是否分页查询
        String pn = map.get("pageNum");
        if (pn != null && !"".equals(pn)) {
            sql += " limit ?,?";
            int pageNum = Integer.parseInt(pn);
            int pageSize = Integer.parseInt(map.get("pageSize"));
            params.add((pageNum - 1) * pageSize);
            params.add(pageSize);
        }
        Object[] obj=params.toArray();
        ResultSet res = JDBCDruidUtil.query(sql, obj);
        while (true){
            try {
                if (!res.next()) break;
                Shop shop=new Shop(
                        res.getInt(1),
                        res.getDate(2),
                        res.getString(3),
                        res.getString(4),
                        res.getInt(5),
                        res.getInt(6),
                        res.getString(7),
                        res.getString(8),
                        res.getString(9),
                        res.getString(10),
                        res.getString(11),
                        res.getString(12),
                        res.getString(13),
                        res.getString(14),
                        res.getString(15),
                        res.getString(16),
                        res.getString(17),
                        res.getInt(18),
                        res.getString(19),
                        res.getString(20),
                        res.getString(21),
                        res.getString(22),
                        res.getInt(23),
                        res.getInt(24),
                        res.getInt(25)
                );
                list.add(shop);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        JDBCDruidUtil.close();
        return list;
    }
    //尚未关联门店总数
    public int selectCountShop(Map<String, String> map) {
        int count = 0;
        String sql = "select count(1) from shop where 1=1";
        List params = new ArrayList<>();
        //判断是否需要进行模糊查询
        String shopName = map.get("shopName");
        if (shopName!=null&&!"".equals(shopName)){
            sql+=" and shopName like ?";
            params.add("%"+shopName+"%");
        };
        sql+=" and (communityId is null or communityId=0) ";
        Object[] obj = params.toArray();
        ResultSet res = JDBCDruidUtil.query(sql, obj);
        while (true) {
            try {
                if (!res.next()) break;
                count = res.getInt(1);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        JDBCDruidUtil.close();
        return count;
    }
    //修改关联门店相关信息
    public int addAssociatedShop(int shopId,int communityId) {
        String sql="update shop set communityId=? where shopId=?";
        Object[] obj={communityId,shopId};
        return JDBCDruidUtil.update(sql, obj);
    }
    public int addAssociatedCommunity(int communityId) {
        String sql="update community set communityShopCount=communityShopCount+1 where communityId=?";
        Object[] obj={communityId};
        return JDBCDruidUtil.update(sql, obj);
    }
//    public List<Shop> findShopListByAdd(Shop shop) {
//        List<Shop> list=new ArrayList<>();
//        List params=new ArrayList();
//        String sql="select * from shop where 1=1 ";
//        String shopName = shop.getShopName();
//        if (shopName!=null&&!"".equals(shopName)){
//            sql+=" and shopName=? ";
//            params.add(shopName);
//        }
//        sql+=" and (communityId is null or communityId=0)";
//        Object[] obj=params.toArray();
//        ResultSet res = JDBCDruidUtil.query(sql, obj);
//        while (true){
//            try {
//                if (!res.next()) break;
//                list.add(shop);
//            } catch (SQLException e) {
//                throw new RuntimeException(e);
//            }
//        }
//        JDBCDruidUtil.close();
//        return list;
//    }

    @Override
    public List<Community> selectAll(Map<String, String> map) {
        List<Community> list = new ArrayList<>();
        //分页查询 模糊查询 时间范围查询
        String sql = "select * from community where 1=1";
        List<Object> params = new ArrayList<>();
        //判断是否需要进行模糊查询
        String communityName = map.get("communityName");
        if (communityName != null && !"".equals(communityName)) {
            sql += " and communityName like ?";
            params.add("%" + communityName + "%");
        }
        //判断是否需要时间范围查询
        String startTime = map.get("startTime");
        if (startTime != null && !"".equals(startTime)) {
            sql += " and createTime between ? and ?";
            String endTime = map.get("endTime");
            params.add(startTime);
            params.add(endTime);
        }
        sql = sql + " order by communityId desc";
        //判断是否需要分页查询
        String pn = map.get("pageNum");
        if (pn != null && !"".equals(pn)) {
            sql += " limit ?,?";
            int pageNum = Integer.parseInt(pn);
            int pageSize = Integer.parseInt(map.get("pageSize"));
            params.add((pageNum - 1) * pageSize);
            params.add(pageSize);
        }
        Object[] obj = params.toArray();
        ResultSet res = JDBCDruidUtil.query(sql, obj);
        while (true) {
            try {
                if (!res.next()) break;
                Community community = new Community(
                        res.getInt(1),
                        res.getString(2),
                        res.getString(3),
                        res.getString(4),
                        res.getString(5),
                        res.getString(6),
                        res.getString(7),
                        res.getString(8),
                        res.getDate(9),
                        res.getInt(10),
                        res.getInt(11),
                        res.getString(12),
                        res.getInt(13),
                        res.getInt(14),
                        res.getString(15)
                );
                list.add(community);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        JDBCDruidUtil.close();
        return list;
    }

    @Override
    public int selectCount(Map<String, String> map) {
        int count = 0;
        //分页查询 模糊查询 时间范围查询
        String sql = "select count(1) from community where 1=1";
        List params = new ArrayList<>();
        //判断是否需要进行模糊查询
        String communityName = map.get("communityName");
        if (communityName != null && !"".equals(communityName)) {
            sql += " and communityName like ?";
            params.add("%" + communityName + "%");
        }
        //判断是否需要时间范围查询
        String startTime = map.get("startTime");
        if (startTime != null && !"".equals(startTime)) {
            sql += " and createTime between ? and ?";
            String endTime = map.get("endTime");
            params.add(startTime);
            params.add(endTime);
        }
        Object[] obj = params.toArray();
        ResultSet res = JDBCDruidUtil.query(sql, obj);
        while (true) {
            try {
                if (!res.next()) break;
                count = res.getInt(1);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        JDBCDruidUtil.close();
        return count;
    }

    @Override
    public List<Shop> associatedStoresList(Map<String, String> map) {
        List<Shop> list=new ArrayList<>();
        List params=new ArrayList<>();
        String sql="select * from shop where communityId = ?";
        int communityId= Integer.parseInt(map.get("communityId"));
        params.add(communityId);
        String num = map.get("pageNum");
        String size= map.get("pageSize");
        if (num!=null&&!"".equals(num)){
            sql+=" limit ?,? ";
            int pageNum=Integer.parseInt(num);
            int pageSize=Integer.parseInt(size);
            params.add(pageNum);
            params.add(pageSize);
        }
        Object[] obj=params.toArray();
        ResultSet res = JDBCDruidUtil.query(sql, obj);
        while (true){
            try {
                if (!res.next()) break;
                Shop shop=new Shop(
                        res.getInt(1),
                        res.getDate(2),
                        res.getString(3),
                        res.getString(4),
                        res.getInt(5),
                        res.getInt(6),
                        res.getString(7),
                        res.getString(8),
                        res.getString(9),
                        res.getString(10),
                        res.getString(11),
                        res.getString(12),
                        res.getString(13),
                        res.getString(14),
                        res.getString(15),
                        res.getString(16),
                        res.getString(17),
                        res.getInt(18),
                        res.getString(19),
                        res.getString(20),
                        res.getString(21),
                        res.getString(22),
                        res.getInt(23),
                        res.getInt(24)
                );
                list.add(shop);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }

        }
        JDBCDruidUtil.close();
        return list;
    }

    @Override
    public int countShop(int communityId) {
        String sql="select count(1) from shop where communityId=?";
        Object[] obj={communityId};
        ResultSet res = JDBCDruidUtil.query(sql, obj);
        int num=0;
        while (true){
            try {
                if (!res.next()) break;
                num=res.getInt(1);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }JDBCDruidUtil.close();
        return num;
    }

    @Override
    public int addAssociatedStores(int Id,int communityId) {
        List<Shop> list=new ArrayList<>();
        int shopId=Id;
        String sql="update shop set communityId=? where shopId=?";
        Object[] obj={communityId,shopId};
        int num = JDBCDruidUtil.update(sql, obj);
        return num;
    }

    @Override
    public List<Shop> findShopListByAdd(Shop shop) {
        List<Shop> list=new ArrayList<>();
        List params=new ArrayList();
        String sql="select * from shop where 1=1 ";
        String shopName = shop.getShopName();
        if (shopName!=null&&!"".equals(shopName)){
            sql+=" and shopName=? ";
            params.add(shopName);
        }
        sql+=" and (communityId is null or communityId=0)";
        Object[] obj=params.toArray();
        ResultSet res = JDBCDruidUtil.query(sql, obj);
        while (true){
            try {
                if (!res.next()) break;
                Shop shop1=new Shop(
                        res.getInt(1),
                        res.getString(7),
                        res.getString(17),
                        res.getInt(18),
                        res.getString(22),
                        res.getInt(24)
                );
                list.add(shop1);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }

        }JDBCDruidUtil.close();
        return list;
    }

    @Override
    public int deleteShopAssociated( int shopId) {
        String sql="update shop set communityId = ? where shopId = ?";
        int communityId=0;
        Object[] obj={communityId,shopId};
        int num = JDBCDruidUtil.update(sql, obj);
        return num;
    }

    @Override
    public int countShops(Map<String,String> map) {
        String sql="select count(0) from shop where communityId=?";
        List params=new ArrayList();
        int communityId = Integer.parseInt(map.get("communityId"));
        params.add(communityId);
        String pn = map.get("pageNum");
        if (pn!=null&&!"".equals(pn)){
            sql+=" limit ?,? ";
            int pageNum=Integer.parseInt(pn);
            int pageSize=Integer.parseInt(map.get("pageSize"));
            params.add(pageNum);
            params.add(pageSize);
        }
        Object[] obj=params.toArray();
        ResultSet res = JDBCDruidUtil.query(sql, obj);
        int num=0;
        while (true){
            try {
                if (!res.next()) break;
                num=res.getInt(1);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return num;
    }
}
